/*
 * DatabaseItemHandler.java
 */

package nxtgeninventorysystem.Model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import nxtgeninventorysystem.Data.Item;
import nxtgeninventorysystem.Data.ItemSearchOptions;
import nxtgeninventorysystem.Data.Person;
import nxtgeninventorysystem.Data.Transaction;
import nxtgeninventorysystem.Data.TransactionSearchOptions;

/**
 * The DatabaseItemHandler class provides general database functionalities to
 * the Item entity including functionalities regarding some of its attributes.
 * @author Seraph Softworks
 */
public final class DatabaseItemHandler {

    public DatabaseItemHandler() {

    }

    public Person getItemPerson(int item_id) {
        Person person = new Person();
        ResultSet result = Database.executeQuery("select distinct person.* from person, item where person.id = item.person and item.id = " + item_id);
        try {
            if(result.next()) {
                person.setId(result.getInt("id"));
                person.setName(result.getString("name"));
                person.setContactNo(result.getString("contact_no"));
                person.setTeam(result.getString("team"));
                return person;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseTransactionHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public ArrayList <Transaction> getItemTransactions(int item_id, TransactionSearchOptions order, boolean ascending) {
        String asc = "";
        if(order.transactionOrderSpecified()) {
            if(ascending)
                asc = "asc";
            else
                asc = "desc";
        }

        ArrayList<Transaction> collection = new ArrayList<Transaction>();
        ResultSet result = Database.executeQuery("select distinct transaction.* from transaction, transactionitem, item, (select transaction.id as 'transaction', COUNT(*) as 'items' from transaction, transactionitem where transactionitem.transaction = transaction.id group by transaction.id) counts where transaction.id = transactionitem.transaction and transactionitem.item = item.id and item.id = " + item_id + order.getTransactionOrderString() + asc);
        try {
            while(result.next()) {
                Transaction transaction = new Transaction();
                transaction.setId(result.getInt("id"));
                transaction.setType(result.getString("type"));
                transaction.setDate(result.getDate("date"));
                transaction.setPurpose(result.getString("purpose"));
                transaction.setPerson(Database.person.readPerson(result.getInt("person")));
                transaction.setItems(Database.transaction.getTransactionItems(result.getInt("id"), new ItemSearchOptions(), true));
                collection.add(transaction);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseTransactionHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

        return collection;
    }

    public int createItem(Item item) {

        if(item == null)
            return 0;

        String query = "insert into item (name, deleted, description, type) values(";
        query += "'" + item.getName().replace("\'","\\'").replace("\"", "\\\"") + "',";
        query += "false,";
        query += "'" + item.getDescription().replace("\'","\\'").replace("\"", "\\\"") + "',";
        query += "'" + item.getType().replace("\'","\\'").replace("\"", "\\\"") + "'";
        query += ")";

        if(! Database.executeUpdate(query)) {
            return 0;
        }
        else {
            ResultSet rst = Database.executeQuery("select LAST_INSERT_ID() as 'last'");
            try {
                if (rst.next()) {
                    return rst.getInt("last");
                }
            } catch (SQLException ex) {
                Logger.getLogger(DatabaseItemHandler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return 0;
    }

    public ArrayList<Item> searchItem(Item values, ItemSearchOptions options, ItemSearchOptions order, boolean ascending) {
        try {
            String query = "select distinct item.* from item, person ";
            String _order = "";
            if (ascending) {
                _order = "asc";
            } else {
                _order = "desc";
            }

            if(options.itemOptionsSpecified())
                query += "where item.deleted = false and ";
            else
                query += "where item.deleted = false ";

            ArrayList <String> conditions = values.getItemSearchParametersQueryStrings(options);

            for (int i = 0; i < conditions.size(); i++) {
                query += conditions.get(i);
                if (i < conditions.size() - 1) {
                    query += " and ";
                }
            }

            if(order != null) {
                query += order.getItemOrderString();
                if(order.itemOrderSpecified())
                    query += _order;
            }

            ArrayList<Item> collection = new ArrayList<Item>();

            System.err.println(query);

            ResultSet result = Database.executeQuery(query);

            while (result.next()) {
                Item item = new Item();
                item.setId(result.getInt("id"));
                item.setName(result.getString("name"));
                item.setPerson(Database.person.readPerson(result.getInt("person")));
                item.setDeleted(result.getBoolean("deleted"));
                item.setDescription(result.getString("description"));
                item.setType(result.getString("type"));
                collection.add(item);
            }
            return collection;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseItemHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Item readItem(int item_id) {
        try {
            Item item = new Item();
            ResultSet result = Database.executeQuery("select distinct * from item where id = " + item_id);

            if(result.next()) {
                item.setId(result.getInt("id"));
                item.setName(result.getString("name"));
                item.setPerson(Database.person.readPerson(result.getInt("person")));
                item.setDeleted(result.getBoolean("deleted"));
                item.setDescription(result.getString("description"));
                item.setType(result.getString("type"));
                return item;
            }

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseItemHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public boolean updateItem(Item values) {
        if(values == null)
            return false;

        String query = "update item set ";
        query += "name = " + "'" + values.getName().replace("\'","\\'").replace("\"", "\\\"") + "',";
        if(values.getPerson() == null)
            query += "person = NULL,";
        else
            query += "person = " + "'" + values.getPerson().getId() + "',";
        query += "deleted = false,";
        query += "description = " + "'" + values.getDescription().replace("\'","\\'").replace("\"", "\\\"") + "',";
        query += "type = " + "'" + values.getType().replace("\'","\\'").replace("\"", "\\\"") + "' ";
        query += "where id = " + "'" + values.getId() + "'";

        return Database.executeUpdate(query);
    }

    public boolean deleteItem(int item_id) {
        return Database.executeUpdate("update item set deleted = true where id = '" + item_id + "'");
    }

    public boolean createItemType(String type_name) {
        return Database.executeUpdate("insert into itemtype (type) values ('" + type_name.replace("'","").replace("\"", "") + "')");
    }

    public ArrayList<String> readItemTypes() {
        try {
            ResultSet result = Database.executeQuery("select distinct * from itemtype");
            ArrayList<String> types = new ArrayList<String> ();
            while (result.next()) {
                types.add(result.getString("type"));
            }
            return types;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseItemHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public boolean updateItemType(String old_name, String new_name) {
        String newName = new_name.replace("\'","\\'").replace("\"", "\\\"");
        String oldName = old_name.replace("\'","\\'").replace("\"", "\\\"");

        return
            //create new team
            createItemType(newName)
                &&
            //change all persons with old team name to new team name
            Database.executeUpdate("update item set type = '" + newName + "' where type = '" + oldName + "'")
                &&
            //delete old team
            Database.executeUpdate("delete from itemtype where type = '" + oldName + "'");

    }

}
